﻿
using Aspose.Cells;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace HYRN.Common
{

    public class OlivingAsposeExcel
    {

        public static System.Data.DataTable ExcelToDatatalbe(string path, int sheetName)//导入
        {
            string target = path;
            Workbook book = new Workbook((target));
            Worksheet sheet = book.Worksheets[sheetName];
            Cells cells = sheet.Cells;
            //获取excel中的数据保存到一个datatable中
            DataTable dt_Import = cells.ExportDataTableAsString(0, 0, cells.MaxDataRow + 1, cells.MaxDataColumn + 1, true);
            return dt_Import;
        }





        public static void ToExcel(string reportPath, string reportName, DataTable dtA, string nameA, DataTable dtB, string nameB)
        {

            string target = reportPath + reportName + ".xls";
            Workbook workBook;

            workBook = new Workbook();

            //清除页先 要不然 新建就有一个sheet
            workBook.Worksheets.Clear();
            workBook.Worksheets.Add(nameA);
            workBook.Worksheets.Add(nameB);

            Worksheet worksheet = workBook.Worksheets[0];
            worksheet.Name = nameA;
            worksheet.Cells.ImportDataTable(dtA, true, "A1");//从A2开始填充数据
            worksheet.AutoFitColumns();//让各列自适应宽度，这个很有用。



            Worksheet worksheetB = workBook.Worksheets[1];
            worksheetB.Name = nameB;
            worksheetB.Cells.ImportDataTable(dtB, true, "A1");//从A2开始填充数据
            worksheetB.AutoFitColumns();//让各列自适应宽度，这个很有用。



            workBook.Save(target, SaveFormat.Excel97To2003);//


            //FileDownload(reportPath, reportName + ".xls");
        }




        #region ToExcel 导出
        /// <summary>
        /// 导出（数据、母版名称，保存的名称）
        /// </summary>
        /// <param name="ds">数据</param>
        /// <param name="reportPath">保存的路径</param>
        /// <param name="reportName">保存的名称( 不包含后缀）默认保存.xls,.html两种</param>
        public static void ToExcel(DataTable dt, string reportPath, string reportName)
        {

            string target = reportPath + reportName + ".xls";
            Workbook workBook;

            workBook = new Workbook();
            Worksheet worksheet = workBook.Worksheets[0];
            worksheet.Name = "检测";
            worksheet.Cells.ImportDataTable(dt, true, "A1");//从A2开始填充数据
            worksheet.AutoFitColumns();//让各列自适应宽度，这个很有用。
            // workBook.Save(target, SaveFormat.Excel97To2003);//
            workBook.Save(target);//

            //FileDownload(reportPath, reportName + ".xls");
        }

        /// <summary>
        /// 导出（数据、母版名称，保存的名称）
        /// </summary>
        /// <param name="ds">数据</param>
        /// <param name="reportPath">保存的路径</param>
        /// <param name="reportName">保存的名称( 不包含后缀）默认保存.xls,.html两种</param>
        public static void ToExcel(DataTable dt, string target)
        {

            Workbook workBook;

            workBook = new Workbook();
            Worksheet worksheet = workBook.Worksheets[0];
            worksheet.Cells.ImportDataTable(dt, true, "A1");//从A2开始填充数据
            worksheet.AutoFitColumns();//让各列自适应宽度，这个很有用。
            workBook.Save(target, SaveFormat.Excel97To2003);//

            //FileDownload(reportPath, reportName + ".xls");
        }



        /// <summary>
        /// 导出（数据、母版名称，保存的名称）
        /// </summary>
        /// <param name="ds">数据</param>
        /// <param name="templatePath">模板的路径</param>
        /// <param name="templateName">模板名称( 包含后缀）</param>
        /// <param name="reportPath">保存的路径</param>
        /// <param name="reportName">保存的名称( 不包含后缀）默认保存.xls,.html两种</param>
        public static void ToExcel(SqlParameter[] spList, DataSet ds, string templatePath, string templateName, string reportPath, string reportName, IList<WorksheetsInsert> ilWorksheetsInsert, IList<TableMerge> ilTableMerge, IList<CellData> ilCellContent)
        {
            string source = templatePath + templateName;
            string target = reportPath + reportName + ".xls";
            string targetHtml = reportPath + reportName + ".html";


            Workbook workBook = new Workbook(source);
            WorkbookDesigner designer = new WorkbookDesigner();
            designer.Workbook = workBook;

            #region 插入行或列
            foreach (WorksheetsInsert wi in ilWorksheetsInsert)
            {
                if (wi.InsertType == 1)
                    workBook.Worksheets[wi.WorksheetIndex].Cells.InsertRows(wi.InsertIndex, wi.InsertTotal);
                else
                    workBook.Worksheets[wi.WorksheetIndex].Cells.InsertColumns(wi.InsertIndex, wi.InsertTotal);
            }
            #endregion

            #region 需要合并单元格
            foreach (TableMerge tm in ilTableMerge)
            {
                workBook.Worksheets[tm.WorksheetIndex].Cells.Merge(tm.FirstRow, tm.FirstColumn, tm.TotalRow, tm.TotalColumn);

                //合并的单元格居中
                Aspose.Cells.Style style = new Aspose.Cells.Style();
                style.HorizontalAlignment = TextAlignmentType.Center;
                StyleFlag sf = new StyleFlag();
                sf.HorizontalAlignment = true;
                workBook.Worksheets[tm.WorksheetIndex].Cells[tm.FirstRow, tm.FirstColumn].SetStyle(style);
            }
            #endregion

            #region 需要在单元格中插入的数据
            foreach (CellData cc in ilCellContent)
            {
                workBook.Worksheets[cc.WorksheetIndex].Cells[cc.InsertRow, cc.InsertColumn].PutValue(cc.CellContent);
            }

            #endregion

            foreach (SqlParameter sp in spList)
            {
                designer.SetDataSource(sp.ParameterName, sp.Value);
            }
            designer.SetDataSource(ds);
            designer.Process();
            //存储为Excel
            designer.Workbook.Save((target), SaveFormat.Excel97To2003);

        }



        #endregion





    }

    #region MyRegion

    /// <summary>
    /// 报表插入行或列
    /// </summary>
    [Serializable]
    public class WorksheetsInsert
    {
        /// <summary>
        /// 报表ID
        /// </summary>
        public int ReportsID { get; set; }

        /// <summary>
        /// 工作表索引
        /// </summary>
        public int WorksheetIndex { get; set; }
        /// <summary>
        /// 插入类型(1行，2列)
        /// </summary>
        public int InsertType { get; set; }
        /// <summary>
        /// 插入起始单元格或列
        /// </summary>
        public int InsertIndex { get; set; }
        /// <summary>
        /// 插入总单元格或列
        /// </summary>
        public int InsertTotal { get; set; }


    }
    /// <summary>
    /// 报表合并单元格
    /// </summary>
    [Serializable]
    public class TableMerge
    {
        /// <summary>
        /// 报表ID
        /// </summary>
        public int ReportsID { get; set; }
        /// <summary>
        /// 工作表索引
        /// </summary>
        public int WorksheetIndex { get; set; }
        /// <summary>
        /// 合并起始行
        /// </summary>
        public int FirstRow { get; set; }
        /// <summary>
        /// 合并起始列
        /// </summary>
        public int FirstColumn { get; set; }
        /// <summary>
        /// 总行数
        /// </summary>
        public int TotalRow { get; set; }
        /// <summary>
        /// 总列数
        /// </summary>
        public int TotalColumn { get; set; }

    }
    /// <summary>
    /// 报表单元格中的数据
    /// </summary>
    [Serializable]
    public class CellData
    {
        /// <summary>
        /// 报表ID
        /// </summary>
        public int ReportsID { get; set; }
        /// <summary>
        /// 工作表索引
        /// </summary>
        public int WorksheetIndex { get; set; }
        /// <summary>
        /// 行
        /// </summary>
        public int InsertRow { get; set; }
        /// <summary>
        /// 列
        /// </summary>
        public int InsertColumn { get; set; }
        /// <summary>
        /// 单元格内容
        /// </summary>
        public string CellContent { get; set; }

    }
    /// <summary>
    /// 获取数据的SQL类型
    /// </summary>
    public enum DataTypeEnum : short
    {
        SQL = 1,
        Procedure = 2
    }


    /// <summary>
    /// 报表类型
    /// </summary>
    public enum ReportTypeEnum : short
    {
        /// <summary>
        /// 年报
        /// </summary>
        Year = 1,
        Month = 2,
        Week = 3,
        Day = 4,
        Squad = 5
    }

    #endregion




}
